/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package controllers.sysAdmin;

import controllers.utils.DatabaseHjelper;
import java.io.Serializable;
import java.sql.Connection;
import java.util.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.inject.Named;
import javax.enterprise.context.SessionScoped;
import models.StudyProgram;

/**
 *
 * @author dybvik
 */
@SessionScoped
@Named(value="studyprogramList")
public class StudyProgramList implements Serializable{
    private final List<StudyProgram> studprogs = new ArrayList<StudyProgram>();
    DatabaseHjelper dbh = DatabaseHjelper.hent();

    /** Creates a new instance of StudyProgramList */
    public StudyProgramList() {
    }

    public String navToStudyprogList(){
        updateStudyProgram();
        return "studyProgramList";
    }
   
    private void updateStudyProgram() {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet res = null;
        try {
            conn = dbh.finnForbindelse();
            stmt = conn.prepareStatement("select * from studyprogram");
            res = stmt.executeQuery();
            synchronized (studprogs) {
                studprogs.clear();
                while (res.next()){
                    StudyProgram sp = new StudyProgram();
                    sp.setStudyProgramID(res.getInt("studyprogramID"));
                    sp.setStudyProgramCode(res.getInt("studyProgramCode"));
                    sp.setName(res.getString("studyProgramName"));
                    sp.getDepartment().setDepartmentID(res.getInt("departmentID"));
                    sp.setApproved(res.getDate("approved"));
                    sp.setLastRevised(res.getDate("lastRevised"));
                    sp.setIsNKRFromInt(res.getInt("isNKR"));
                    sp.setApprovedBy(res.getString("approvedBy"));
                    studprogs.add(sp);
                }
                for(StudyProgram sp: studprogs){
                    stmt = conn.prepareStatement("select * from department where departmentID=?");
                    System.out.println("Studprogets dep-ID: " +sp.getDepartment().getDepartmentID());
                    stmt.setInt(1, sp.getDepartment().getDepartmentID());
                    res = stmt.executeQuery();
                    while(res.next()){
                        sp.getDepartment().setName(res.getString("departmentName"));
                        System.out.println("Studprogets dep-name: " +sp.getDepartment().getName());
                    }
                    
                }
                
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        } finally {
            DatabaseHjelper.lukk(res);
            DatabaseHjelper.lukk(stmt);
            DatabaseHjelper.lukk(conn);
        }
    }

    public void executeChanges() {
        for (StudyProgram sp : studprogs) {
            if (sp.isEditable()) {
                saveChanges(sp);
                sp.setEditable(false);
            }
        }
    }

    private void saveChanges(final StudyProgram sp) {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = dbh.finnForbindelse();
            stmt = conn.prepareStatement("UPDATE studyprogram SET studyProgramCode=?, studyProgramName=?, approved=?, lastRevised=?, isNKR=?, approvedBy=? WHERE studyProgramID=?");
            stmt.setInt(1, sp.getStudyProgramCode());
            stmt.setString(2, sp.getName());
            stmt.setDate(3, javaDateToSqlDate(sp.getApproved()));
            stmt.setDate(4, javaDateToSqlDate(sp.getLastRevised()));
            stmt.setInt(5, sp.isIsNKRAsInt());
            stmt.setString(6, sp.getApprovedBy());
            stmt.setInt(7, sp.getStudyProgramID());
            stmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println("Feil i lagring av Department: " + e);
        } finally {
            DatabaseHjelper.lukk(stmt);
            DatabaseHjelper.lukk(conn);
        }
    }

    public String revertFlagsAndNavigate(){
        for(StudyProgram sp: studprogs){
            if(sp.isEditable()) sp.setEditable(false);
            if(sp.isDeleteFlag()) sp.setDeleteFlag(false);
        }
        return navToStudyprogList();
    }


    public String setDeleteFlagAndNavigate(StudyProgram sp) {
        sp.setDeleteFlag(true);
        return "confirmDeleteStudyProgram";
    }


    public String executeDelete(){
        for (StudyProgram sp: studprogs) {
            if (sp.isDeleteFlag()) {
                deleteStudyprogram(sp);
                studprogs.remove(sp);
                break;
            }
        }
        return navToStudyprogList();
    }

    private void deleteStudyprogram(StudyProgram sp){
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = dbh.finnForbindelse();
            stmt = conn.prepareStatement("DELETE FROM studyprogram WHERE studyprogramID=?");
            stmt.setInt(1, sp.getStudyProgramID());
            stmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println("Feil i sletting av StudyProgram: " + e);
        } finally {
            DatabaseHjelper.lukk(stmt);
            DatabaseHjelper.lukk(conn);
        }
    }

    private java.sql.Date javaDateToSqlDate(Date d) {
        long ms = d.getTime();
        java.sql.Date sqlDato = new java.sql.Date(ms);
        return sqlDato;
    }

    public List<StudyProgram> getStudprogs() {
        return studprogs;
    }
}
